import pymysql
from pymysql.cursors import DictCursor
# from DBUtils.PooledDB import PooledDB
import logging
from typing import List, Dict, Optional, Tuple

from spider.common.util.StringUtil import StrUtil

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
)
logger = logging.getLogger(__name__)


class SafeMySQLCRUD:
    """支持特殊字符处理的MySQL CRUD工具类，基于DBUtils连接池"""

    # def __init__(self,
    #              host: str,
    #              port: int,
    #              user: str,
    #              password: str,
    #              db: str,
    #              charset: str = 'utf8mb4',
    #              maxconnections: int = 10):
    #     """初始化数据库连接池"""
    #     self.pool = PooledDB(
    #         creator=pymysql,
    #         maxconnections=maxconnections,
    #         mincached=2,
    #         maxcached=5,
    #         blocking=True,
    #         host=host,
    #         port=port,
    #         user=user,
    #         password=password,
    #         database=db,
    #         charset=charset,
    #         cursorclass=DictCursor,
    #         autocommit=False  # 关闭自动提交，手动控制事务
    #     )

    def __init__(self, host='localhost', port=3306, user='root', password='178121', database='db2024'):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.converter = StrUtil()
        self.connection = pymysql.connect(host=self.host, user=self.user,port=self.port ,password=self.password,db=self.database,charset='utf8')


    def _get_connection(self):
        """获取数据库连接（内部使用）"""
        try:
            return self.connection
        except Exception as e:
            logger.error(f"获取数据库连接失败: {str(e)}")
            raise

    @staticmethod
    def _escape_special_chars(value: any) -> any:
        """
        转义特殊字符（作为参数化查询的补充保护）
        处理包含单引号、双引号、反斜杠等特殊字符的字符串
        """
        if isinstance(value, str):
            # 使用pymysql的转义函数处理特殊字符
            return pymysql.converters.escape_string(value)
        return value

    def insert(self, table: str, data: Dict[str, any]) -> int:
        """
        插入单条数据（自动处理特殊字符）
        :param table: 表名
        :param data: 数据字典 {字段: 值}，支持包含特殊字符的字符串
        :return: 自增ID
        """
        if not table or not data:
            raise ValueError("表名和数据不能为空")

        # 转义所有字符串类型的值
        safe_data = {k: self._escape_special_chars(v) for k, v in data.items()}

        fields = ", ".join(safe_data.keys())
        placeholders = ", ".join(["%s"] * len(safe_data))
        sql = f"INSERT INTO `{table}` ({fields}) VALUES ({placeholders})"
        values = tuple(safe_data.values())

        conn = None
        cursor = None
        try:
            conn = self._get_connection()
            cursor = conn.cursor()
            cursor.execute(sql, values)
            last_id = cursor.lastrowid
            conn.commit()
            logger.info(f"插入成功 [表: {table}, ID: {last_id}]")
            return last_id
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error(f"插入失败 [表: {table}, 错误: {str(e)}]")
            raise

    def batch_insert(self, table: str, data_list: List[Dict[str, any]]) -> int:
        """批量插入数据（处理特殊字符）"""
        if not table or not data_list:
            raise ValueError("表名和数据列表不能为空")

        # 检查字段一致性
        first_keys = set(data_list[0].keys())
        for item in data_list[1:]:
            if set(item.keys()) != first_keys:
                raise ValueError("批量插入的数据字典字段必须一致")

        # 转义所有字符串值
        safe_data_list = [
            {k: self._escape_special_chars(v) for k, v in item.items()}
            for item in data_list
        ]

        fields = ", ".join(safe_data_list[0].keys())
        placeholders = ", ".join(["%s"] * len(safe_data_list[0]))
        sql = f"INSERT INTO `{table}` ({fields}) VALUES ({placeholders})"
        values_list = [tuple(item.values()) for item in safe_data_list]

        conn = None
        cursor = None
        try:
            conn = self._get_connection()
            cursor = conn.cursor()
            affected = cursor.executemany(sql, values_list)
            conn.commit()
            logger.info(f"批量插入成功 [表: {table}, 条数: {len(data_list)}]")
            return affected
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error(f"批量插入失败 [表: {table}, 错误: {str(e)}]")
            raise

    def update(self, table: str, data: Dict[str, any], where: Tuple[str, tuple]) -> int:
        """更新数据（处理特殊字符）"""
        if not table or not data or not where:
            raise ValueError("表名、更新数据和条件不能为空")
        # 转义更新数据中的特殊字符
        safe_data = {k: self._escape_special_chars(v) for k, v in data.items()}

        set_clause = ", ".join([f"`{k}` = %s" for k in safe_data.keys()])
        where_sql, where_params = where
        # 转义条件中的字符串参数
        safe_where_params = tuple(self._escape_special_chars(p) for p in where_params)

        sql = f"UPDATE `{table}` SET {set_clause} WHERE {where_sql}"
        values = tuple(safe_data.values()) + safe_where_params
        conn = None
        cursor = None
        try:
            conn = self._get_connection()
            cursor = conn.cursor()
            affected = cursor.execute(sql, values)
            conn.commit()
            logger.info(f"更新成功 [表: {table}, 影响行数: {affected}]")
            return affected
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error(f"更新失败 [表: {table}, 错误: {str(e)}]")
            raise

    def delete(self, table: str, where: Tuple[str, tuple]) -> int:
        """删除数据"""
        if not table or not where:
            raise ValueError("表名和条件不能为空")

        where_sql, where_params = where
        # 转义条件参数中的特殊字符
        safe_where_params = tuple(self._escape_special_chars(p) for p in where_params)

        sql = f"DELETE FROM `{table}` WHERE {where_sql}"

        conn = None
        cursor = None
        try:
            conn = self._get_connection()
            cursor = conn.cursor()
            affected = cursor.execute(sql, safe_where_params)
            conn.commit()
            logger.info(f"删除成功 [表: {table}, 影响行数: {affected}]")
            return affected
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error(f"删除失败 [表: {table}, 错误: {str(e)}]")
            raise
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    def query_one(self, sql: str, params: tuple = None) -> Optional[Dict[str, any]]:
        """查询单条数据（处理特殊字符参数）"""
        # 转义查询参数中的特殊字符
        safe_params = tuple(self._escape_special_chars(p) for p in (params or ()))

        conn = None
        cursor = None
        try:
            conn = self._get_connection()
            cursor = conn.cursor()
            cursor.execute(sql, safe_params)
            result = cursor.fetchone()
            return result
        except Exception as e:
            logger.error(f"查询失败 [SQL: {sql}, 错误: {str(e)}]")
            raise
        finally:
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    def retrieve_jsons(self, table, condition='', show_sql=False):
        query = f"SELECT * FROM {table}"
        if condition:
            query += f" WHERE {condition}"
        if show_sql:
            print(query)
        jsons = self.execute_query2(query)
        jsons = [{self.converter.snake_to_camel(key): value for key, value in json.items()} for json in jsons]
        return jsons

    def execute_query2(self, query):
        result = None
        try:
            with self.connection.cursor() as cursor:
                cursor.execute(query)
                columns = [column[0] for column in cursor.description]
                result = [dict(zip(columns, row)) for row in cursor.fetchall()]
        except pymysql.Error as e:
            print(f"Error executing query: {e}")
        return result

    def retrieve_count(self, table, condition='', show_sql=False):
        query = f"SELECT count(1) FROM {table}"
        if condition:
            query += f" WHERE {condition}"
        if show_sql:
            print(query)
        try:
            return self.execute_query(query)[0][0]
        except:
            return 0


# 使用示例（包含特殊字符处理测试）
if __name__ == "__main__":
    # 初始化工具类（替换为实际数据库配置）
    db = SafeMySQLCRUD(
        host="localhost",
        port=3306,
        user="root",
        password="your_password",
        db="test_db"
    )

    try:
        # 创建测试表（包含文本字段用于测试特殊字符）
        db.query_one("""
        CREATE TABLE IF NOT EXISTS articles (
            id INT PRIMARY KEY AUTO_INCREMENT,
            title VARCHAR(255) NOT NULL,
            content TEXT,
            create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        """)

        # 测试插入包含特殊字符的数据
        special_chars_data = {
            "title": "测试'单引号\"双引号\\反斜杠的标题",
            "content": "这是包含\n换行符、\t制表符、'单引号'、\"双引号\"和\\反斜杠\\的内容"
        }

        # 插入带特殊字符的数据
        article_id = db.insert("articles", special_chars_data)
        print(f"插入带特殊字符的文章ID: {article_id}")

        # 查询验证
        article = db.query_one("SELECT * FROM articles WHERE id = %s", (article_id,))
        print("\n查询结果验证:")
        print(f"标题: {article['title']}")
        print(f"内容: {article['content']}")

        # 测试更新带特殊字符的数据
        update_data = {
            "title": "更新后的标题包含;分号#井号`反引号"
        }
        db.update("articles", update_data, ("id = %s", (article_id,)))

        # 批量插入测试
        batch_data = [
            {
                "title": "批量1: 包含&符号和<尖括号>",
                "content": "测试批量插入中的特殊字符处理"
            },
            {
                "title": "批量2: 包含'单引号和\"双引号",
                "content": "第二行测试数据"
            }
        ]
        db.batch_insert("articles", batch_data)
        print("\n批量插入带特殊字符的数据成功")

    except Exception as e:
        print(f"操作失败: {str(e)}")
